package site.itaoke.config;

import com.alibaba.fastjson.JSONObject;
import com.jfinal.core.Controller;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.upload.UploadFile;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

/**
 * Created by sunfeihu on 2017/9/13.
 */
public class IndexController extends Controller {

    public void index() {
        redirect("/login");
    }

    public void activity() {
        String uuid = this.getPara("uuid");
        Record record = Db.findFirst(" select * from activitys where uuid = ? ", new Object[]{
                uuid
        });
        this.setAttr("obj", record);
        render("/activity.ftl");
    }

    public void search() throws UnsupportedEncodingException {
        String name = this.getPara("name");
        redirect("http://taobao.itaoke.site/index.php?r=l&kw=" + URLEncoder.encode(name, "utf-8"));
    }

    public void activitys() {
        if (!"true".equals(this.getSessionAttr("login"))) {
            renderText("nopower");
            return;
        }
        List<Record> records = Db.find(" select * from activitys order by id desc ");
        this.setAttr("records", records);
        render("/activitys.ftl");
    }

    public void orders() {
        if (!"true".equals(this.getSessionAttr("login"))) {
            renderText("nopower");
            return;
        }
        render("/orders.ftl");
    }

    public void orderspage() {
        if (!"true".equals(this.getSessionAttr("login"))) {
            renderText("nopower");
            return;
        }
        Integer page = this.getParaToInt("page");
        Integer rows = this.getParaToInt("rows");
        Page<Record> records = Db.paginate(page, rows, " select * ", " from orders order by create_time desc ");
        JSONObject obj = new JSONObject();
        obj.put("total", records.getTotalRow());
        obj.put("rows", records.getList());
        renderJson(obj);
    }

    public void aupload() {
        if (!"true".equals(this.getSessionAttr("login"))) {
            renderText("nopower");
            return;
        }
        this.setAttr("aid", this.getPara("aid"));
        render("/aupload.ftl");
    }

    public void upload() {
        if (!"true".equals(this.getSessionAttr("login"))) {
            renderText("nopower");
            return;
        }
        render("/upload.ftl");
    }

    public void doAUpload() throws IOException {
        if (!"true".equals(this.getSessionAttr("login"))) {
            renderText("nopower");
            return;
        }
        UploadFile uploadFile = this.getFile();
        File file = uploadFile.getFile();
        String uploadPath = this.getSession().getServletContext().getRealPath("/upload");
        File folder = new File(uploadPath);
        if (!folder.exists()) {
            folder.mkdirs();
        }
        String uuid = UUID.randomUUID().toString();
        uuid = uuid.replaceAll("-", "") + ".jpg";
        FileUtils.moveFile(file, new File(uploadPath + "/" + uuid));
        String aid = this.getPara("aid");
        Db.update(" update activitys set erwm = ? where id = ? ", new Object[]{
                "/upload/" + uuid, aid
        });
        renderText("ok");
    }

    public void report() {
        if (!"true".equals(this.getSessionAttr("login"))) {
            renderText("nopower");
            return;
        }
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String startDate = sdf.format(DateUtils.getBeginDayOfSMonth());
        String startStr = this.getPara("start");
        String endDate = sdf.format(DateUtils.getEndDayOfSMonth());
        String endStr = this.getPara("end");
        if (null != startStr && !"".equals(startStr)) {
            startDate = startStr + " 00:00:00";
        }
        if (null != endStr && !"".equals(endStr)) {
            endDate = endStr + " 23:59:59";
        }
        this.setAttr("startDate", startDate);
        this.setAttr("endDate", endDate);
        String sql = "SELECT a.tgid,a.nickname ,( SELECT count(*) AS counts FROM orders WHERE a.tgid = orders.ad_pos_id AND( orders.order_state = '订单付款' OR orders.order_state = '订单结算') AND close_date BETWEEN '" + startDate + "' AND '" + endDate + "') AS '订单付款数' ,( SELECT sum(pay_price) AS pay_price FROM orders WHERE a.tgid = orders.ad_pos_id AND( orders.order_state = '订单付款' OR orders.order_state = '订单结算') AND close_date BETWEEN '" + startDate + "' AND '" + endDate + "') AS '订单付款金额' ,( SELECT count(*) AS counts FROM orders WHERE a.tgid = orders.ad_pos_id AND orders.order_state = '订单结算' AND close_date BETWEEN '" + startDate + "' AND '" + endDate + "') AS '订单结算数' ,( SELECT sum(commission_price + subsidy_price) AS csprice FROM orders WHERE a.tgid = orders.ad_pos_id AND orders.order_state = '订单结算' AND close_date BETWEEN '" + startDate + "' AND '" + endDate + "') AS '订单佣金' ,( SELECT count(*) AS counts FROM orders WHERE a.tgid = orders.ad_pos_id AND orders.order_state = '订单失效' AND close_date BETWEEN '" + startDate + "' AND '" + endDate + "') AS '订单失效数' FROM users a";
        List<Record> records = Db.find(sql);
        Double heji = 0D;
        for (Record r : records) {
            if (null != r.getBigDecimal("订单佣金")) {
                heji += r.getBigDecimal("订单佣金").doubleValue();
            }
        }
        this.setAttr("heji", (heji / 2) * 0.9);
        this.setAttr("records", records);
        render("/report.ftl");
    }

    public void doUpload() throws IOException {
        if (!"true".equals(this.getSessionAttr("login"))) {
            renderText("nopower");
            return;
        }
        UploadFile uploadFile = this.getFile();
        File file = uploadFile.getFile();
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row = null;
        HSSFCell cell = null;
        List<Record> list = new ArrayList<Record>();
        Record record = null;
        for (int i = 1; i < sheet.getLastRowNum(); i++) {
            row = sheet.getRow(i);
            record = new Record();
            for (int j = 0; j < row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                if (j == 0) {//create_time 创建时间 STRING
                    record.set("create_time", cell.getStringCellValue());
                } else if (j == 1) {//click_time 点击时间 STRING
                    record.set("click_time", cell.getStringCellValue());
                } else if (j == 2) {//goods_name 商品信息 STRING
                    record.set("goods_name", cell.getStringCellValue());
                } else if (j == 3) {//goods_id 商品ID STRING
                    record.set("goods_id", cell.getStringCellValue());
                } else if (j == 4) {//wangwang 掌柜旺旺 STRING
                    record.set("wangwang", cell.getStringCellValue());
                } else if (j == 5) {//shop 所属店铺 STRING
                    record.set("shop", cell.getStringCellValue());
                } else if (j == 6) {//goods_counts 商品数量 NUMERIC
                    record.set("goods_counts", cell.getNumericCellValue());
                } else if (j == 7) {//goods_price 商品单价 NUMERIC
                    record.set("goods_price", cell.getNumericCellValue());
                } else if (j == 8) {//order_state 订单状态 STRING
                    record.set("order_state", cell.getStringCellValue());
                } else if (j == 9) {//order_type 订单类型 STRING
                    record.set("order_type", cell.getStringCellValue());
                } else if (j == 10) {//income_proportion 收入比率 STRING
                    record.set("income_proportion", cell.getStringCellValue());
                } else if (j == 11) {//proportion 分成比率 STRING
                    record.set("proportion", cell.getStringCellValue());
                } else if (j == 12) {//pay_price 付款金额 NUMERIC
                    record.set("pay_price", cell.getNumericCellValue());
                } else if (j == 13) {//estimate 效果预估 NUMERIC
                    record.set("estimate", cell.getNumericCellValue());
                } else if (j == 14) {//close_price 结算金额 NUMERIC
                    record.set("close_price", cell.getNumericCellValue());
                } else if (j == 15) {//estimate_revenue 预估收入 NUMERIC
                    record.set("estimate_revenue", cell.getNumericCellValue());
                } else if (j == 16) {//close_date 结算时间 STRING
                    if ("".equals(cell.getStringCellValue())) {
                        record.set("close_date", null);
                    } else {
                        record.set("close_date", cell.getStringCellValue());
                    }
                } else if (j == 17) {//commission_rate 佣金比率 STRING
                    record.set("commission_rate", cell.getStringCellValue());
                } else if (j == 18) {//commission_price 佣金金额 NUMERIC
                    record.set("commission_price", cell.getNumericCellValue());
                } else if (j == 19) {//subsidy_proportion 补贴比率 STRING
                    record.set("subsidy_proportion", cell.getStringCellValue());
                } else if (j == 20) {//subsidy_price 补贴金额 NUMERIC
                    record.set("subsidy_price", cell.getNumericCellValue());
                } else if (j == 21) {//subsidy_type 补贴类型 STRING
                    record.set("subsidy_type", cell.getStringCellValue());
                } else if (j == 22) {//done_platform 成交平台 STRING
                    record.set("done_platform", cell.getStringCellValue());
                } else if (j == 23) {//third_party_source 第三方服务来源 STRING
                    record.set("third_party_source", cell.getStringCellValue());
                } else if (j == 24) {//order_no 订单编号 STRING
                    record.set("order_no", cell.getStringCellValue());
                } else if (j == 25) {//category_name 类目名称 STRING
                    record.set("category_name", cell.getStringCellValue());
                } else if (j == 26) {//source_media_id 来源媒体ID STRING
                    record.set("source_media_id", cell.getStringCellValue());
                } else if (j == 27) {//source_media_name 来源媒体名称 STRING
                    record.set("source_media_name", cell.getStringCellValue());
                } else if (j == 28) {//ad_pos_id 广告位ID STRING
                    record.set("ad_pos_id", cell.getStringCellValue());
                } else if (j == 29) {//ad_pos_name 广告位名称 STRING
                    record.set("ad_pos_name", cell.getStringCellValue());
                }
                list.add(record);
            }
        }
        for (Record r : list) {
            Record czr = Db.findFirst(" select * from orders where order_no = ? ", new Object[]{
                    r.getStr("order_no")
            });
            //如果不存在，进行增加
            if (null == czr) {
                Db.save("orders", r);
            } else {//如果存在，进行修改
                r.set("id", czr
                        .getLong("id"));
                Db.update("orders", "id", r);
            }
        }
        wb.close();
        fs.close();
        FileUtils.deleteQuietly(file);
        renderText("ok");
    }

    public void agents() {
        String openid = this.getPara("openid");
        String tj = this.getPara("tj");
        this.setAttr("openid", openid);
        this.setAttr("tj", tj);
        Record user = Db.findFirst(" select * from users where openid = ? ", new Object[]{
                openid
        });
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String startDate = sdf.format(DateUtils.getBeginDayOfMonth());
        String endDate = sdf.format(DateUtils.getEndDayOfMonth());
        if ("sy".equals(tj)) {
            startDate = sdf.format(DateUtils.getBeginDayOfSMonth());
            endDate = sdf.format(DateUtils.getEndDayOfSMonth());
        }
        if (null != user) {
            String sql = "SELECT nickname ,( SELECT ifnull(sum(commission_price + subsidy_price),0) AS csprice FROM orders WHERE a.tgid = orders.ad_pos_id AND orders.order_state = '订单结算' and orders.close_date between '" + startDate + "' and '" + endDate + "' ) AS yj FROM users a WHERE referrer_id = ? ";
            this.setAttr("records", Db.find(sql, new Object[]{user.getLong("id")}));
            render("/agents.ftl");
        } else {
            renderText("error");
            return;
        }
    }

    public void moneys() {
        String openid = this.getPara("openid");
        String tj = this.getPara("tj");
        this.setAttr("openid", openid);
        this.setAttr("tj", tj);
        Record user = Db.findFirst(" select * from users where openid = ? ", new Object[]{
                openid
        });
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String startDate = sdf.format(DateUtils.getBeginDayOfSMonth());
        String endDate = sdf.format(DateUtils.getEndDayOfSMonth());
        String whereDateStr = " and close_date between '" + startDate + "' and '" + endDate + "' ";
        whereDateStr = "";
        if (null != user) {
            List<Record> records = null;
            String[] tgidArr = user.getStr("tgid").split(",");
            String sql = "";
            String whereStr = "";
            if (null == tj || "".equals(tj) || "yx".equals(tj)) {
                if (tgidArr.length > 1) {
                    whereStr += " and ( ad_pos_id = '" + tgidArr[0] + "' ";
                    for (int i = 1; i < tgidArr.length; i++) {
                        whereStr += "  or ad_pos_id = '" + tgidArr[i] + "' ";
                    }
                    whereStr += " ) ";
                    sql = " select * from orders where 1 = 1 " + whereStr + " " + whereDateStr + " and order_state = '订单付款' order by create_time desc ";
                    records = Db.find(sql);
                } else {
                    records = Db.find(" select * from orders where ad_pos_id = ? and order_state = '订单付款' " + whereDateStr + " order by create_time desc ", new Object[]{
                            user.getStr("tgid")
                    });
                }
            } else if ("sx".equals(tj)) {
                if (tgidArr.length > 1) {
                    whereStr += " and ( ad_pos_id = '" + tgidArr[0] + "' ";
                    for (int i = 1; i < tgidArr.length; i++) {
                        whereStr += "  or ad_pos_id = '" + tgidArr[i] + "' ";
                    }
                    whereStr += " ) ";
                    sql = " select * from orders where 1 = 1 " + whereStr + " and order_state = '订单失效' " + whereDateStr + " order by create_time desc ";
                    records = Db.find(sql);
                } else {
                    records = Db.find(" select * from orders where ad_pos_id = ? and order_state = '订单失效' " + whereDateStr + " order by create_time desc ", new Object[]{
                            user.getStr("tgid")
                    });
                }
            } else if ("js".equals(tj)) {
                if (tgidArr.length > 1) {
                    whereStr += " and ( ad_pos_id = '" + tgidArr[0] + "' ";
                    for (int i = 1; i < tgidArr.length; i++) {
                        whereStr += "  or ad_pos_id = '" + tgidArr[i] + "' ";
                    }
                    whereStr += " ) ";
                    sql = " select * from orders where 1 = 1 " + whereStr + " and order_state = '订单结算' " + whereDateStr + " order by close_date desc ";
                    records = Db.find(sql);
                } else {
                    records = Db.find(" select * from orders where ad_pos_id = ? and order_state = '订单结算' " + whereDateStr + " order by close_date desc ", new Object[]{
                            user.getStr("tgid")
                    });
                }
                if (null != records) {
                    Double heji = 0D;
                    Double _he = 0D;
                    for (Record r : records) {
                        if (null != r.getBigDecimal("commission_price")) {
                            _he = r.getBigDecimal("commission_price").doubleValue() + r.getBigDecimal("subsidy_price").doubleValue();
                            heji += _he;
                        }
                    }
                    this.setAttr("heji", (heji / 2) * 0.9);
                }
            }
            this.setAttr("records", records);
            render("/moneys.ftl");
        } else {
            renderText("error");
            return;
        }
    }

    public void login() {
        render("/login.ftl");
    }

    public void dologin() {
        String usern = this.getPara("usern");
        String pwd = this.getPara("pwd");
        if ("admin".equals(usern) && "admin123!@#".equals(pwd)) {
            this.setSessionAttr("login", "true");
        }
        renderText("ok");
    }

    public void list() {
        if (!"true".equals(this.getSessionAttr("login"))) {
            renderText("nopower");
            return;
        }
        render("/list.ftl");
    }

    public void page() {
        if (!"true".equals(this.getSessionAttr("login"))) {
            renderText("nopower");
            return;
        }
        Integer page = this.getParaToInt("page");
        Integer rows = this.getParaToInt("rows");
        Page<Record> records = Db.paginate(page, rows, " select * ", " from users order by id desc ");
        JSONObject obj = new JSONObject();
        obj.put("total", records.getTotalRow());
        obj.put("rows", records.getList());
        renderJson(obj);
    }

    public void delete() {
        if (!"true".equals(this.getSessionAttr("login"))) {
            renderText("nopower");
            return;
        }
        Integer id = this.getParaToInt("id");
        Db.deleteById("users", id);
        renderText("ok");
    }

    public void buysdelete() {
        if (!"true".equals(this.getSessionAttr("login"))) {
            renderText("nopower");
            return;
        }
        Integer id = this.getParaToInt("id");
        Db.deleteById("moneys", id);
        renderText("ok");
    }

    public void buys() {
        if (!"true".equals(this.getSessionAttr("login"))) {
            renderText("nopower");
            return;
        }
        this.setAttr("userid", this.getPara("userid"));
        render("/buys.ftl");
    }

    public void buysdetail() {
        if (!"true".equals(this.getSessionAttr("login"))) {
            renderText("nopower");
            return;
        }
        this.setAttr("userid", this.getPara("userid"));
        render("/buysdetail.ftl");
    }

    public void buyspage() {
        if (!"true".equals(this.getSessionAttr("login"))) {
            renderText("nopower");
            return;
        }
        Integer userid = this.getParaToInt("userid");
        Integer page = this.getParaToInt("page");
        Integer rows = this.getParaToInt("rows");
        Page<Record> records = Db.paginate(page, rows, " select * ", " from moneys where userid = ? order by id desc ", new Object[]{
                userid
        });
        JSONObject obj = new JSONObject();
        obj.put("total", records.getTotalRow());
        obj.put("rows", records.getList());
        renderJson(obj);
    }

    public void update() {
        if (!"true".equals(this.getSessionAttr("login"))) {
            renderText("nopower");
            return;
        }
        Integer id = this.getParaToInt("id");
        String tgid = this.getPara("tgid");
        String nickname = this.getPara("nickname");
        String phone = this.getPara("phone");
        String groupname = this.getPara("groupname");
        Integer referrer_id = this.getParaToInt("referrer_id");
        String level = this.getPara("level");
        Db.update(" update users set tgid = ? , nickname = ? , phone = ? , groupname = ? , referrer_id = ? , level = ? where id = ?", new Object[]{
                tgid, nickname, phone, groupname, referrer_id, level, id
        });
        renderText("ok");
    }

    public void buyssave() {
        if (!"true".equals(this.getSessionAttr("login"))) {
            renderText("nopower");
            return;
        }
        Integer userid = this.getParaToInt("userid");
        String money = this.getPara("money");
        String goodsname = this.getPara("goodsname");
        String paydate = this.getPara("paydate");
        Db.update(" insert into moneys (money,paydate,userid,goodsname) values (?,?,?,?)", new Object[]{
                money, paydate, userid, goodsname
        });
        renderText("ok");
    }

    public void detail() {
        if (!"true".equals(this.getSessionAttr("login"))) {
            renderText("nopower");
            return;
        }
        Integer id = this.getParaToInt("id");
        this.setAttr("obj", Db.findById("users", id));
        render("/detail.ftl");
    }

}